﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace VIT.Provider
{
    public class Category : Category_Base
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Constructor
        public Category(int id=-1,
                        string parentId = "",
                        string languageId = "",
                        string image = "",
                        string title = "",
                        string description = "",
                        int orders = 50,
                        bool published = true)
        {
            this.Id = id;
            this.ParentId = parentId;
            this.LanguageId = languageId;
            this.Image = image;
            this.Title = title;
            this.Description = description;
            this.Orders = orders;
            this.Published = published;
        }
        #endregion

        #region List
        public DataTable GetAll(string categorylist = "", bool isTransaction = false)
        {
            string strQuery = string.Empty;
            if(categorylist=="")
                strQuery = "select Id, Title, ParentId, Image, Description, Orders, Published from Category c left join Category_Language l on Id=CategoryId and LanguageID=@LanguageID where Deleted=0";
            else
                strQuery = "select Id, Title, ParentId, Image, Description, Orders, Published from Category c left join Category_Language l on Id=CategoryId and LanguageID=@LanguageID where Deleted=0 and Id in (" + categorylist + ")";
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            return ExecuteDataset(isTransaction, strQuery, LanguageId).Tables[0];
        }

        /* If Category has children or has article then it can't be deleted */
        public bool checkForDel(int id, bool isTransaction = false)
        {
            string strQuery = "select count(Id) from Category where ParentId=@Id and Deleted=0";
            SqlParameter Id = new SqlParameter("@Id", id);
            int count = int.Parse(ExecuteScalar(isTransaction, strQuery, Id).ToString());
            if (count > 0) return false; // has child

            strQuery = "select count(Article.Id) from Category, Article where CategoryId=@Id";
            count = int.Parse(ExecuteScalar(isTransaction, strQuery, Id).ToString());
            if (count > 0) return false; // has article
            return true;
            
        }

        public bool Delete(int delete, int id, bool isTransaction = false)
        {
            string strQuery = "update Category set Deleted=@Deleted, Published=@Published, ModifyDate=@Now, ModifyByUser=@User, DeleteByUser=@DeleteByUser where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter D = new SqlParameter("@Deleted", delete);
            SqlParameter P = new SqlParameter("@Published", (delete == 0) ? 1 : 0);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter DeleteByUser = new SqlParameter("@DeleteByUser", (delete==1)?user:"");
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            int result = ExecuteNonQuery(isTransaction, strQuery, Id, D, P, User, DeleteByUser, Now);
            return result > 0;
        }

        public bool Publish(int publish, int id, bool isTransaction = false)
        {
            string strQuery = "update Category set Published=@Published, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", publish);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            int result = ExecuteNonQuery(isTransaction, strQuery, Id, P, User, Now);
            return result > 0;
        }

        public bool Order(string order, int id, bool isTransaction = false)
        {
            string strQuery = "update Category set Orders=Orders " + order + " 1, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            int result = ExecuteNonQuery(isTransaction, strQuery, Id, User, Now);
            return result > 0;
        }
        #endregion

        #region Edit
        public void GetSingle(bool isTransaction = false)
        {
            if (Exist(isTransaction))
            {
                string strQuery = "select ParentId, Title, Image, Description, Orders, Published from Category c left join Category_Language l on Id=CategoryId and LanguageID=@LanguageId where Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = ExecuteDataset(isTransaction, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Image = dt.Rows[0]["Image"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.Title = dt.Rows[0]["Title"].ToString();
                    this.Description = dt.Rows[0]["Description"].ToString();
                    this.ParentId = dt.Rows[0]["ParentId"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];
                }
            }
            else this.Id = -1;
        }

        public bool Exist(bool isTransaction = false)
        {
            string strQuery = "select Id from Category where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(isTransaction, strQuery, Id) != null;
        }

        public bool Exist(int id, string languageid, bool isTransaction = false)
        {
            string strQuery = "select CategoryId, LanguageID from Category_Language where CategoryId=@Id and LanguageID=@LanguageId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", languageid);
            return ExecuteScalar(isTransaction, strQuery, Id, LanguageId) != null;
        }

        public bool Save(bool isTransaction = true)
        {
            TransactionBegin();
            try
            {
                if (Exist(isTransaction)) // sửa
                {
                    Delete(0, this.Id, isTransaction);
                    UpdateObj(isTransaction);

                    if (Exist(this.Id, this.LanguageId, isTransaction)) UpdateObjLang(isTransaction);
                    else InsertObjLang(isTransaction);
                }
                else // thêm
                {
                    this.Id = InsertObj(isTransaction);
                    InsertObjLang(isTransaction);
                }

                TransactionCommit();
                return true;
            }
            catch 
            { 
                TransactionRollback();
                return false;
            }
        }

        public int InsertObj(bool isTransaction = false)
        {
            string strQuery = "insert into Category(ParentId, Image, Orders, Published, Deleted, CreateDate, ModifyDate, CreateByUser, ModifyByUser) "
                                     + "values (@ParentId, @Image, 50, 1, 0, @Now, @Now, @User, @User)";

            SqlParameter ParentId = new SqlParameter("@ParentId", this.ParentId);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, ParentId, Image, Orders, Published, User, Now);
        }

        public int InsertObjLang(bool isTransaction = false)
        {
            string sqlLang = "insert into Category_Language (CategoryId, LanguageId, Title, Description, CreateDate, ModifyDate, CreateByUser, ModifyByUser) "
                                                + "values (@CategoryId, @LanguageId, @Title, @Description, @Now, @Now, @User, @User)";
            SqlParameter CategoryId = new SqlParameter("@CategoryId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);
            return ExecuteNonQuery(isTransaction, sqlLang, CategoryId, LanguageId, Title, Description, User, Now);
        }

        public int UpdateObj(bool isTransaction = false)
        {
            string sqlCat = "update Category set ParentId=@ParentId, Image=@Image, Orders=@OrderVal, Published=@Published, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter ParentId = new SqlParameter("@ParentId", this.ParentId);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter Orders = new SqlParameter("@OrderVal", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlCat, Id, ParentId, Image, Orders, Published, User, Now);
        }

        public int UpdateObjLang(bool isTransaction = false)
        {
            string sqlLang = "update Category_Language set Title=@Title, Description=@Description, ModifyDate=@Now, ModifyByUser=@User where CategoryId=@CategoryId and LanguageId=@LanguageId";
            SqlParameter CategoryId = new SqlParameter("@CategoryId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlLang, CategoryId, LanguageId, Title, Description, User, Now);
        }
        #endregion

        #region Trash
        public string getParentId(int delete, int id, bool isTransaction = false)
        {
            string strQuery = "select parent.Id from Category chuild, Category parent where chuild.Id=@Id and parent.Deleted=@Delete and parent.Id=chuild.ParentId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter d = new SqlParameter("@Delete", delete);
            object obj = ExecuteScalar(isTransaction, strQuery, Id, d);
            return (obj != null) ? obj.ToString() : "";
        }

        public DataTable GetRecycleBin(string SortColumn, bool isTransaction = false)
        {
            string strQuery;
            if(SortColumn == "")
                strQuery = "select Id, ParentId, Image, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Category where Deleted=1";
            else
                strQuery = "select Id, ParentId, Image, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Category where Deleted=1 order by " + SortColumn;
            return ExecuteDataset(isTransaction, strQuery).Tables[0];
        }

        public bool Cleared(int id, bool isTransaction = false)
        {
            string strQuery = "delete from Category where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            return ExecuteNonQuery(isTransaction, strQuery, Id) > 0;
        }
        #endregion
    }
}
